import json

dict_content={'name':"jack"}
with open('JSON文件写入.json', 'w')as f:
    json.dump(dict_content,f)

with open('JSON文件.json', 'r', encoding='utf-8-sig') as f:
    str = f.read()

data = json.loads(str)
print(data)

name_age = data['people']
print(name_age)
target_name = name_age[1]['name']
target_age = name_age[1]['age']

print(target_name + ':' + target_age)

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', database="test")
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS zzh')
sql = """CREATE TABLE`zzh`(
    `id`int(10) NOT NULL AUTO_INCREMENT,
    `first_name`char(20) NOT NULL,
    `last_name`char(20) DEFAULT NULL,
    `age`int(11)DEFAULT NULL,
    `sex`char(1)DEFAULT NULL,
    `income`float DEFAULT NULL,
    PRIMARY KEY(`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"""
cursor.execute(sql)
print("Created table Successfull.")
db.close()

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, database='test')
cursor=db.cursor()
sql="UPDATE EMPLOYEE SET AGE=AGE + 1 WHERE SEX='%c'" % ('M')
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, database='test')
cursor=db.cursor()
sql="DELETE FROM EMPLOYEE WHERE AGE>'%d'"%(40)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, database='test')
cursor = db.cursor()
sql = "SELECT*FROM EMPLOYEE WHERE INCOME >%d" % (1000)
try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        fname = row[1]
        lname = row[2]
        age = row[3]
        sex = row[4]
        income = row[5]
        print("name= % s % s ,age= % s ,sex= % s,income = %s" % (fname, lname, age, sex, income))

except:
    import traceback

    traceback.print_exc()
    print("Error:unable to fetch data")

db.close()